Calculation Functions

Function Index

Mathematical Functions

abs(x)

Returns the absolute value of x.

=abs(-1) returns 1

acos(x)

Returns the arccosine of x.

=acos(-1) returns 3.14159265358979 (=pi())

acosh(x)

Returns the inverse hyperbolic cosine of x.

=acosh(10) returns 2.99322284612638

asin(x)

Returns the arcsine of x.

=asin(1) returns 1.5707963267949 (=pi()/2)

asinh(x)

Returns the inverse hyperbolic sine of x.

=asinh(10) returns 2.99822295029797

atan(x)

Returns the arctangent of x (-PI/2, PI/2).

=atan(1) returns 0.78539816339745 (=pi()/4)

atan2(x, y)

Returns the arctangent of y/x (-PI, PI). If x is 0 or if both x and y are zero, atan2 returns 0.

=atan2(1, 1) returns 0.78539816339745 (=pi()/4)

atanh(x)

Returns the inverse hyperbolic tangent of x.

=atanh(0.76159415595576) returns 0.9(9)

ceiling(x, m)

Rounds up x to the nearest integer or the nearest multiplicity of m.

=ceiling(5.1, 1) returns 6

=ceiling(-5.1, 1) returns -6.

CEILING.MATH(number, [significance], [mode])

Rounds number up to the nearest multiple of significance.

If significance is omitted, it defaults to 1. If significance is negative, its absolute value is used.

If mode is omitted or 0, negative numbers are rounded away from zero. If mode is 1, negative numbers are rounded toward zero.

Examples:
CEILING.MATH(5.1, 1) returns 6
CEILING.MATH(-5.1, 1) returns -6
CEILING.MATH(-5.1, 1, 1) returns -5
CEILING.MATH(7.2, 0.5) returns 7.5

CEILING.PRECISE(number, [significance])

Rounds number up to the nearest multiple of significance, always away from zero.

If significance is omitted, it defaults to 1. The sign of significance is ignored; its absolute value is used.

Examples:
CEILING.PRECISE(5.1) returns 6
CEILING.PRECISE(-5.1) returns -6
CEILING.PRECISE(7.2, 0.5) returns 7.5

combin(n, k)

Returns the number of k-element combinations for a k-element set.

=combin(8, 2) returns 28

combin2(n, k)

Returns the number of k-element combinations with repeating elements for a n-element set.

=combin2(8, 2) returns 36

cos(x)

Returns the cosine of x. The argument is in radians.

=cos(pi()) returns -1

cosh(x)

Returns the hyperbolic cosine of x.

=cosh(1) returns 1.54308063481524

degress(x)

Converts radians to degrees.

=degrees(pi()/2) returns 90

even(x)

Rounds x up (away from zero) to the nearest even integer.

=even(1.5) returns 2

=even(-1) returns -2

exp(x)

Raises e to the power of x.

=exp(1) returns 2.71828182845905

fact(n)

Returns the factorial of n. The argument must be in the range <1,170>.

=fact(6) returns 720

factDouble(n)

If n is an even number, factDouble returns the product n(n-2)(n-4)...(4)(2).
If n is an odd number, factDouble returns the product n(n-2)(n-4)...(3)(1).
The argument must be in the range <1,170>.

=factDouble(6) returns 48

floor(x, m)

Rounds (down) x to the nearest integer or the nearest multiplicity of m.

=floor(5.1, 1) returns 5

=floor(-5.1, 1) returns -5.

FLOOR.MATH(number, [significance], [mode])

Rounds number down to the nearest multiple of significance.

If significance is omitted, it defaults to 1. If significance is negative, its absolute value is used.

If mode is omitted or 0, negative numbers are rounded away from zero. If mode is 1, negative numbers are rounded toward zero.

Examples:
FLOOR.MATH(5.1, 1) returns 5
FLOOR.MATH(-5.1, 1) returns -5
FLOOR.MATH(-5.1, 1, 1) returns -6
FLOOR.MATH(7.2, 0.5) returns 7.0

FLOOR.PRECISE(number, [significance])

Rounds number down to the nearest multiple of significance, always toward zero.

If significance is omitted, it defaults to 1. The sign of significance is ignored; its absolute value is used.

Examples:
FLOOR.PRECISE(5.1) returns 5
FLOOR.PRECISE(-5.1) returns -5
FLOOR.PRECISE(7.2, 0.5) returns 7.0

gcd(v1, v2, ...)

Returns the greatest common divisor for a given list of arguments (numbers/arrays of numbers).

=gcd(32, 24) returns 8.

=gcd({24,64,32}, 128) returns 8.

int(x)

Rounds (down) x to the nearest integer.

=int(5.1) returns 5

=int(-5.1) returns -6.

ISO.CEILING(number)

Rounds number up to the nearest integer, always away from zero, following ISO 605 rounding rules.

Equivalent to CEILING.PRECISE(number, 1).

Examples:
ISO.CEILING(5.1) returns 6
ISO.CEILING(-5.1) returns -6
ISO.CEILING(2.0) returns 2

lcm(v1, v2, ...)

Returns the least common multiple for a given list of arguments (numbers/array of numbers).

=lcm(8, 7) returns 56

=lcm({16, 44, 32}, 11) returns 352.

ln(x)

Returns the natural logarithm of x.

=ln(2.71828182845905) returns 1

log(x, [y])

Returns the logarithm of x to the base y. If y is omitted, it's assumed to be 10.

=log(10,) returns 1

log10(x)

Returns the base-10 logarithm of x.

=log10(100) returns 2

mDeterm(m)

Returns the determinant of the square matrix m.

=mDeterm({8,5,-2; 2,3,1; 3,-1,-3}) returns 3

mInverse(m)

Returns the inverse of the square matrix m.

=mInverse({8,5,-2; 2,3,1; 3,-1,-3}) returns {-2.66666666666667, 5.66666666666667, 3.66666666666667; 3, -6, -4; -3.66666666666667, 7.66666666666667, 4.66666666666667}

mMult(v1, v2, ...)

Multiplies the specified matrices.

=mMult({1,2,1;0,2,1}, {1;1;2}, {2,1,1}*2) returns {20, 10, 10; 16, 8, 8}

mod(x, y)

Returns the floating point remainder of x/y.

=mod(10, 3)    returns 1

=mod(-3, 2)    returns -1

=mod(3, -2)    returns 1

=mod(-3, -2)    returns -1

mRound(x, m)

Rounds x to the multiplicity of m, where x and m have the same sign.

=mRound(10, 3) returns 9

=mRound(1.3, 0.2) returns 1.4

multinomial(number1, number2, ...)

Returns the ratio (number1+number2+...)!/(number1!number2!...).

=multinomial(2, 3, 4) returns 1260.

odd(x)

Rounds x up (away from zero) to the nearest odd integer.

=odd(6) returns 7

=odd(-4) returns -5

pi()

Returns the value of PI (3.14159265358979).

power(x, y)

Returns x raised to the power of y.

product(v1, v2, ...)

Multiplies the specified arguments which can be numbers, text representations of numbers or arrays/ranges. Empty cells are ignored. If there are no valid numbers, the function returns the #N/A! error value.

=product(3, 4, {1, 5}) returns 60

productIfs(data_range, if_range1, criteria1 [, if_range2, criteria2, ...])

Multiplies numbers that meet the specified criteria. All text strings in the specified 'data_range' array/range are ignored. All arrays/ranges must have the same number of columns and rows. If there are no valid numbers (to multiple), the function returns the #N/A! error value.
The criteria can be one of the following:

(1) a number,

(2) a text string beginning with the =,>,>=,<,<=,<> operators followed by an unformatted number, a generic date/time string (YYYY-MM-DD) or the & followed by a single cell address,

(3) a text string optionally containing wildcard characters '?' (any character) or '*' (any string, including an empty string). To search for ? or * place a tilde (~) before them.

=productIfs({1,2;3,2}, {4,5;2,7}, 2)    returns 3

=productIfs({1,2;3,4}, {4,5;2,7}, ">2", {4,5;2,7}, "<7")    returns 2

=productIfs({2, 4; 3, 1}, {"abcde","def";"abc","a"}, "?bc*")    returns 6

=productIfs($a$1:$a$1000, $d$1:$d$1000, ">"&f2, $c$1:$c$1000, "<="&f3)    multiplies values greater than value from the cell F2 and not greater than the F3 cell value.

quotient(x, y)

Returns the integer part of x/y.

=quotient(5, 2) returns 2

=quotient(-10, 3) returns -3

radians(x)

Converts degrees to radians.

=radians(180) returns 3.14159265358979 (=pi())

rand()

Returns an evenly distributed number in the range <0, 1). A new number is returned each time the workbook is updated.

See the mtxRand function.

randBetween(x1, x2)

Returns an evenly distributed number in the range <x1, x2). A new number is returned each time the workbook is updated.

round(x, [digits])

round(x)

Rounds x to the specified number of digits. If the 'digits' argument is greater than 0, then x is rounded to the specified number of decimal places. If it's 0, then x is rounded to the nearest integer. If it's less than 0, then x is rounded to the left of the decimal point. The default value for 'digits' is 0.

=round(2.6,)    returns 3

=round(2.15, 1)    returns 2.2

=round(2.149, 1)    returns 2.1

=round(-1.475, 2)    returns -1.48

=round(123.5, -2)    returns 100

=round(2.6)    returns 3

=round(2.15)    returns 2

roundDown(x, [digits])

Rounds x down (towards 0) to the specified number of digits. If the 'digits' argument is greater than 0, then x is rounded to the specified number of decimal places. If it's 0, then x is rounded to the nearest integer. If it's less than 0, then x is rounded to the left of the decimal point. The default value for 'digits' is 0.

=roundDown(2.6,)    returns 2

=roundDown(2.15, 1)    returns 2.1

=roundDown(2.7, 0)    returns 2

=roundDown(-2.7, 0)    returns -2

roundUp(x, [digits])

Rounds x up (away from zero) to the specified number of digits. If the 'digits' argument is greater than 0, then x is rounded to the specified number of decimal places. If it's 0, then x is rounded to the nearest integer. If it's less than 0, then x is rounded to the left of the decimal point. The default value for 'digits' is 0.

=roundUp(2.6,)    returns 3

=roundUp(2.15, 1)    returns 2.2

=roundUp(2.7, 0)    returns 3

=roundUp(-2.7, 0)    returns -3

seriesSum(x, n, m, a)

For the given numbers x, n, m and array a, seriesSum returns the value of the power series:

a(1)x^n + a(2)x^(n+m) + a(3)x^(n+2m) + ... + a(l)x^(n+(l-1)m)

where l is the number of cells in the array a and a(i) is the i-th cell (row-wise) in the array a.

The polynomial:
5 + 2*x + 3*x^3

=seriesSum(2, 0, 1, {5, 2, 0, 3}) returns 33

sign(x)

Returns 1 if x is positive, 0 if x equals 0 and -1 if x is negative.

=sign(-5) returns -1

=sign(5) returns 5

sin(x)

Returns the sin of x. The argument is in radians.

=sin(pi()/2) returns 1

sinh(x)

Returns the hyperbolic sine of x.

=sinh(1) returns 1.1752011936438

sqrt(x)

Returns the square root of x.

=sqrt(81) returns 9

sqrtPi(x)

Returns the square root of x*PI.

=sqrtPi(1) returns 1.77245385090552

subTotal(v1, v2, ...)

Calculate a subtotal for the specified arguments which can be numbers or arrays of numbers. Cells containing formulas with another "subTotal" function references are ignored.

sum(v1, v2, ...)

Adds the specified arguments. Arguments that are either text strings which can't be converted to numbers or errors cause an error.

Text strings in arrays or cell/range references are ignored. To include the text representations of numbers, use the sumA() function.

=sum(3, 4, {1, 5}) returns 13

=sum(3, 4, {1, "5"}) returns 8

sumA(v1, v2, ...)

Adds the specified arguments. Arguments that are either text strings which can't be converted to numbers or errors cause an error.

Text representations of numbers in arrays or cell/range references are included. Other text strings are ignored. To exclude text strings entirely, use the sum() function.

=sumA(3, 4, {1, 5}) returns 13

=sumA(3, 4, {1, "5"}) returns 13

sumIf(if_range, criteria, [data_range])

Adds numbers that meet the specified criteria. All text strings are ignored. If the 'data_range' range is omitted, cells from the 'if_range' range are used. The 'sum_range' and 'if_range' ranges must have the same number of columns and rows. The criteria can be one of the following:

(1) a number,

(2) a text string beginning with the =,>,>=,<,<=,<> operators followed by an unformatted number, a generic date/time string (YYYY-MM-DD) or the & followed by a single cell address,

(3) a text string optionally containing wildcard characters '?' (any character) or '*' (any string, including an empty string). To search for ? or * place a tilde (~) before them.

=sumIf({1,2;3,2}, 2,)    returns 4

=sumIf({1,2;3,4}, ">2",)    returns 7

=sumIf({1,2;3,4}, ">"&b4,)    returns a sum based on the b4 cell value

=sumIf({"abcde","def";"abc","a"}, "?bc*", {2, 4; 3, 1})    returns 5

=sumIf({"abcde","def";"bc","a"}, "bc", {2, 4; 3, 1})    returns 3

=sumIf({"ABcde","def";"Bc","a"}, ">=bc", {2, 4; 3, 1})    returns 7

sumAIfs(data_range, if_range1, criteria1 [, if_range2, criteria2, ...])

Adds numbers from the 'data_range' that meet a number of criteria. Text representations of numbers are included. All other text strings are ignored.
All ranges must have the same number of columns and rows. The criteria can be one of the following:

(1) a number,

(2) a text string beginning with the =,>,>=,<,<=,<> operators followed by an unformatted number, a generic date/time string (YYYY-MM-DD) or the & followed by a single cell address,

(3) a text string optionally containing wildcard characters '?' (any character) or '*' (any string, including an empty string). To search for ? or * place a tilde (~) before them.

=sumAIfs({1,2;3,2}, {4,5;2,7}, 2)    returns 3

=sumAIfs({1,2;3,4}, {4,5;2,7}, ">2", {4,5;2,7}, "<7")    returns 3

=sumIfs({2, 4; 3, 1}, {"abcde","def";"abc","a"}, "?bc*")    returns 5

=sumAIfs($a$1:$a$1000, $d$1:$d$1000, ">"&f2, $c$1:$c$1000, "<="&f3)    sums values greater than value from the cell F2 and not greater than the F3 cell value.

sumIfs(data_range, if_range1, criteria1 [, if_range2, criteria2, ...])

Adds numbers from the 'data_range' array/ that meet a number of criteria. All text strings 'data_range' are ignored. All ranges must have the same number of columns and rows. The criteria can be one of the following:

(1) a number,

(2) a text string beginning with the =,>,>=,<,<=,<> operators followed by an unformatted number, a generic date/time string (YYYY-MM-DD) or the & followed by a single cell address,

(3) a text string optionally containing wildcard characters '?' (any character) or '*' (any string, including an empty string). To search for ? or * place a tilde (~) before them.

=sumIfs({1,2;3,2}, {4,5;2,7}, 2)    returns 3

=sumIfs({1,2;3,4}, {4,5;2,7}, ">2", {4,5;2,7}, "<7")    returns 3

=sumIfs({2, 4; 3, 1}, {"abcde","def";"abc","a"}, "?bc*")    returns 5

=sumIfs($a$1:$a$1000, $d$1:$d$1000, ">"&f2, $c$1:$c$1000, "<="&f3)    sums values greater than value from the cell F2 and not greater than the F3 cell value.

sumProduct(v1, v2, ...)

Multiplies numbers from the specified 'v1','v2',... arrays and add the obtained products. Empty cells are ignored.

=sumProduct({1,3}, {2,4}) returns 14

sumSq(v1, v2, ...)

Adds squares of all the specified arguments which can be numbers or arrays of numbers.

=sumSq(3, 4, {1, 5}) returns 51

=sumSq(true, 2) returns 5

sumX2mY2(x_array, y_array)

Returns the sum of the difference of squares of numbers from two arrays. Empty cells are treated as 0 values.

=sumX2mY2({2,5}, {6,1}) returns -8

sumX2pY2(x_array, y_array)

Returns the sum of the sum of squares of numbers from two arrays. Empty cells are treated as 0 values.

=sumX2pY2({2,5}, {6,1}) returns 66

sumXmY2(x_array, y_array)

Returns the sum of squares of differences of numbers from two arrays. Empty cells are treated as 0 values.

=sumXmY2({2,5}, {6,1}) returns 32

tan(x)

Returns the tangent of x. The argument is in radians.

=tan(pi()/4) returns 1

tanh(x)

Returns the hyperbolic tangent of x.

=tanh(1) returns 0.76159415595576

trunc(x, [digits])

trunc(x)

Removes the fractional part of x to the precision specified by the number of digits to the left of the decimal point. The default value of the 'digit' argument is 0.

=trunc(8.9,)    returns 8

=trunc(-8.9,)    returns -8

=trunc(1.256, 2)    returns 1.25

=trunc(8.9)    returns 8

=trunc(1.256)    returns 1